import logging
import math
from typing import Optional, List

from fastapi import APIRouter, Depends, Query, Path
from fastapi.security import oauth2
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.engine import create
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session
from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme, EncryptionMiddleware

data_mrk_head_router = APIRouter(prefix="/material/mrkHead", tags=["物料损益管理"],
                                 dependencies=[Depends(oauth2_scheme)])


class MaterialProfitLossSearch(PaginationBase):
    # 筛选条件
    fsiteNo: Optional[str] = Field("", description="站点编号")
    ftakedateStart: Optional[str] = Field("", description="损益开始日期(yyyyMMdd)")
    ftakedateEnd: Optional[str] = Field("", description="损益结束日期(yyyyMMdd)")
    fbilltype: Optional[str] = Field("", description="单据类型编码(不等于3)")
    fmrkuser: Optional[str] = Field("", description="制单人")
    fstockno: Optional[str] = Field("", description="仓库编号")
    fbillno: Optional[str] = Field("", description="单据编号(支持前缀匹配)")
    fbillstate: Optional[str] = Field("", description="单据状态编码")


@data_mrk_head_router.get("/list", summary="物料损益查询接口")
def list_mrk_head(search: MaterialProfitLossSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from i_tc_mrk_head where fbilltype<>3"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = search.fmrkuser
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,"
    sql = sql + " fexplanation,fupddate,foperator,fcheckdate,fcheckuser,fbillstate,fbilltype"
    sql = sql + " from i_tc_mrk_head where fbilltype<>3"
    if search.fsiteNo != "":
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = search.fsiteNo
    if search.fbillno != "":
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmrkuser != "":
        sql = sql + " and fmrkuser = :fmrkuser "
        param["fmrkuser"] = search.fmrkuser
    if search.fstockno != "":
        sql = sql + " and fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fbilltype != "":
        sql = sql + " and fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql = sql + " and to_char(fmrkdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    if search.order == "desc":
        sql = sql + " order by  fmrkdate,fbillno desc "
    else:
        sql = sql + " order by  fmrkdate,fbillno  "
    sql = sql + " limit :limit offset :offset "
    param["limit"] = search.limit
    param["offset"] = search.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.frfidbarcode,b.fgoodno,b.fbuyprice,b.fqty,b.ftotamt,"
    sql_3 = sql_3 + " b.fboxbarcode,b.fcabbarcode,b.foperator,b.fupddate,b.fmemo,b.fprocdate,"
    sql_3 = sql_3 + " c.fgoodname"
    sql_3 = sql_3 + " from i_tc_mrk_head a "
    sql_3 = sql_3 + " left join i_tc_mrk_detail b on a.fbillno=b.fbillno"
    sql_3 = sql_3 + " left join i_ts_good_info c  on b.fgoodno=c.fgoodno"
    sql_3 = sql_3 + " where fbilltype<>3"
    if search.fsiteNo != "":
        sql_3 = sql_3 + " and a.fsite_no = :site_no"
        param["site_no"] = search.fsiteNo
    if search.fbillno != "":
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = search.fbillno
    if search.fmrkuser != "":
        sql_3 = sql_3 + " and a.fmrkuser = :fmrkuser "
        param["fmrkuser"] = search.fmrkuser
    if search.fstockno != "":
        sql_3 = sql_3 + " and a.fstockno = :fstockno "
        param["fstockno"] = search.fstockno
    if search.fbilltype != "":
        sql_3 = sql_3 + " and a.fbilltype = :fbilltype "
        param["fbilltype"] = search.fbilltype
    if search.fbillstate != "":
        sql_3 = sql_3 + " and a.fbillstate = :fbillstate "
        param["fbillstate"] = search.fbillstate
    if (search.ftakedateStart != "") and (search.ftakedateEnd != ""):
        sql_3 = sql_3 + " and to_char(a.fmrkdate,'yyyymmdd') between :ftakedateStart  and :ftakedateEnd "
        param["ftakedateStart"] = search.ftakedateStart
        param["ftakedateEnd"] = search.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline"
    # print(sql_3)
    data_detail = db.execute(text(sql), param).fetchall()

    data_list = []
    for key in range(len(data)):
        fmrkdate = ""
        try:
            fmrkdate = data[key][3].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fmrkdate == ""):
            fmrkdate = None

        fupddate = ""
        try:
            fupddate = data[key][9].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fupddate == ""):
            fupddate = None

        fcheckdate = ""
        try:
            fcheckdate = data[key][11].strftime('%Y-%m-%dT%H:%M:%S')
        except Exception as e:
            print(e)

        if (fcheckdate == ""):
            fcheckdate = None

        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['fmrkuser'] = data[key][2]
        d1['fmrkdate'] = fmrkdate
        d1['fstockno'] = data[key][4]
        d1['fitemcount'] = data[key][5]
        d1['fpiececount'] = data[key][6]
        d1['ftotalamt'] = data[key][7]
        d1['fexplanation'] = data[key][8]
        d1['fupddate'] = fupddate
        d1['foperator'] = data[key][10]
        d1['fcheckdate'] = fcheckdate
        d1['fcheckuser'] = data[key][12]
        d1['fbillstate'] = data[key][13]
        d1['fbilltype'] = data[key][14]
        sbillno = data[key][0]
        # 过滤数据
        filtered_rows = [row for row in data_detail if row[0] == sbillno]
        detail_list = []
        for j1 in range(len(filtered_rows)):
            tupddate = ""
            try:
                tupddate = filtered_rows[j1][10].strftime('%Y-%m-%dT%H:%M:%S')
            except Exception as e:
                print(e)
            if (tupddate == ""):
                tupddate = None
            tprocdate = ""
            try:
                tprocdate = filtered_rows[j1][12].strftime('%Y-%m-%dT%H:%M:%S')
            except Exception as e:
                print(e)
            if (tprocdate == ""):
                tprocdate = None
            d2 = {}
            d2['fbillno'] = filtered_rows[j1][0]
            d2['fline'] = filtered_rows[j1][1]
            d2['frfidbarcode'] = filtered_rows[j1][2]
            d2['fgoodno'] = filtered_rows[j1][3]
            d2['fbuyprice'] = filtered_rows[j1][4]
            d2['fqty'] = filtered_rows[j1][5]
            d2['ftotamt'] = filtered_rows[j1][6]
            d2['fboxbarcode'] = filtered_rows[j1][7]
            d2['fcabbarcode'] = filtered_rows[j1][8]
            d2['foperator'] = filtered_rows[j1][9]
            d2['fupddate'] = tupddate
            d2['fmemo'] = filtered_rows[j1][11]
            d2['fprocdate'] = tprocdate
            d2['fgoodname'] = filtered_rows[j1][13]
            detail_list.append(d2)
        d1["details"] = detail_list
        data_list.append(d1)
    info_dict = {"list": data_list,
                 "limit": search.limit,
                 "page": search.pageIndex,
                 "pages": (count + search.limit - 1) // search.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class ConsumableScrapDetail(BaseModel):
    """耗材报废明细"""
    fline: int = Field(..., description="明细行号")
    frfidbarcode: str = Field(..., description="RFID条码")
    fgoodno: str = Field(..., description="耗材编号")
    fbuyprice: float = Field(..., description="单价")
    fqty: float = Field(..., description="数量")
    ftotamt: float = Field(..., description="金额")
    fboxbarcode: str = Field(..., description="箱条码")
    fcabbarcode: str = Field(..., description="柜条码")
    fmemo: str = Field(..., description="备注")
    fprocdate: Optional[str] = Field(None, description="处理日期")
    foperator: str = Field(..., description="操作人")
    fupddate: Optional[str] = Field(None, description="更新日期")


class ConsumableScrapCreate(BaseModel):
    """耗材报废新增请求参数"""
    fbillno: str = Field(..., description="单据编号")
    fmrkdate: str = Field(..., description="制单日期(yyyy-MM-dd)")
    fmrkuser: str = Field(..., description="制单人")
    fsiteNo: str = Field(..., description="站点编号")
    fstockno: str = Field(..., description="仓库编号")
    fbilltype: Optional[str] = Field("1", description="单据类型编码")
    fexplanation: Optional[str] = Field("", description="报废说明")
    details: List[ConsumableScrapDetail] = Field(..., description="报废明细列表")


@data_mrk_head_router.post("/create", summary="耗材报废新增接口")
def create_mrk_head(create: ConsumableScrapCreate, db: Session = Depends(get_db)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = create.fbillno
        fmrkdate = create.fmrkdate
        fmrkuser = create.fmrkuser
        fsiteNo = create.fsiteNo
        fstockno = create.fstockno
        d1 = {}
        with db.begin():
            detail_list = []
            details = create.details
            for detail in details:
                fqty = detail.fqty
                fbuyprice = detail.fbuyprice
                ftotamt = float(fbuyprice) * float(fqty)

                sql = "insert into i_tc_mrk_detail(fbillno,fline,frfidbarcode,fgoodno,fbuyprice,fqty,ftotamt,fboxbarcode,fcabbarcode,fupddate,foperator,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :frfidbarcode , :fgoodno , :fbuyprice , :fqty , :ftotamt , :fboxbarcode , :fcabbarcode ,now(), :fmrkuser , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": detail.fline,
                    "frfidbarcode": detail.frfidbarcode,
                    "fgoodno": detail.fgoodno,
                    "fbuyprice": detail.fbuyprice,
                    "fqty": fqty,
                    "ftotamt": ftotamt,
                    "fboxbarcode": detail.fboxbarcode,
                    "fcabbarcode": detail.fcabbarcode,
                    "fmrkuser": fmrkuser,
                    "fprocdate": detail.fprocdate,
                    "fmemo": detail.fmemo,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + float(fqty)
                ftotalamt = ftotalamt + float(ftotamt)

                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = detail.fline
                d2['frfidbarcode'] = detail.frfidbarcode
                d2['fgoodno'] = detail.fgoodno
                d2['fbuyprice'] = float(fbuyprice)
                d2['fqty'] = float(fqty)
                d2['ftotamt'] = float(ftotamt)
                d2['fmemo'] = detail.fmemo
                d2['fprocdate'] = detail.fprocdate
                d2['fboxbarcode'] = detail.fboxbarcode
                d2['fcabbarcode'] = detail.fcabbarcode
                d2['foperator'] = fmrkuser
                d2['fupddate'] = None
                detail_list.append(d2)
            sql = "insert into i_tc_mrk_head(fbillno,fsite_no,fmrkuser,fmrkdate,fstockno,fitemcount,fpiececount,ftotalamt,fexplanation,fupddate,foperator,fbillstate,fbilltype)"
            sql = sql + " values( :fbillno , :fsiteNo , :fmrkuser , :fmrkdate , :fstockno , :fitemcount , :fpiececount , :ftotalamt , :fexplanation ,now(), :fmrkuser ,1, :fbilltype )"
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": fsiteNo,
                "fmrkuser": fmrkuser,
                "fmrkdate": fmrkdate,
                "fstockno": fstockno,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "fexplanation": create.fexplanation,
                "fbilltype": create.fbilltype,
            })
            # 返回
            d1['fbillno'] = fbillno
            d1['fsiteno'] = fsiteNo
            d1['fmrkuser'] = fmrkuser
            d1['fmrkdate'] = fmrkdate
            d1['fstockno'] = fstockno
            d1['fitemcount'] = fitemcount
            d1['fpiececount'] = fpiececount
            d1['ftotalamt'] = float(ftotalamt)
            d1['fexplanation'] = create.fexplanation
            d1['fupddate'] = fmrkdate
            d1['foperator'] = fmrkuser
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fbillstate'] = 1
            d1['fbilltype'] = create.fbilltype
            d1["details"] = detail_list
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_mrk_head_router.post("/update", summary="耗材报废修改接口")
def update_mrk_head(update: ConsumableScrapCreate, db: Session = Depends(get_db), token: str = Depends(oauth2_scheme)):
    try:
        fitemcount = 0
        fpiececount = 0
        ftotalamt = 0
        fbillno = update.fbillno
        fmrkdate = update.fmrkdate
        fmrkuser = update.fmrkuser
        fsiteNo = update.fsiteNo
        fstockno = update.fstockno
        user_id = EncryptionMiddleware.get_current_user(token)
        with db.begin():
            sql = "delete from i_tc_mrk_detail where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            details = update.details
            for detail in details:
                fqty = detail.fqty
                fbuyprice = detail.fbuyprice
                ftotamt = float(fbuyprice) * float(fqty)

                sql = "insert into i_tc_mrk_detail(fbillno,fline,frfidbarcode,fgoodno,fbuyprice,fqty,ftotamt,fboxbarcode,fcabbarcode,fupddate,foperator,fprocdate,fmemo)"
                sql = sql + " values( :fbillno , :fline , :frfidbarcode , :fgoodno , :fbuyprice , :fqty , :ftotamt , :fboxbarcode , :fcabbarcode ,now(), :fmrkuser , :fprocdate , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": detail.fline,
                    "frfidbarcode": detail.frfidbarcode,
                    "fgoodno": detail.fgoodno,
                    "fbuyprice": detail.fbuyprice,
                    "fqty": fqty,
                    "ftotamt": ftotamt,
                    "fboxbarcode": detail.fboxbarcode,
                    "fcabbarcode": detail.fcabbarcode,
                    "fmrkuser": fmrkuser,
                    "fprocdate": detail.fprocdate,
                    "fmemo": detail.fmemo,
                })
                fitemcount = fitemcount + 1
                fpiececount = fpiececount + float(fqty)
                ftotalamt = ftotalamt + float(ftotamt)
            sql = "update i_tc_mrk_head "
            sql = sql + " set fstockno= :fstockno ,fbilltype= ：fbilltype ,fexplanation= ：fexplanation "
            sql = sql + ",fmrkuser= :fmrkuser ,fmrkdate= :fmrkdate "
            sql = sql + ",fitemcount= :fitemcount ,fpiececount= :fpiececount ,ftotalamt= :ftotalamt "
            sql = sql + ",fupddate=now(),foperator= :userid "
            sql = sql + " where fbillno= :fbillno "
            db.execute(text(sql), {
                "fstockno": fstockno,
                "fbilltype": update.fbilltype,
                "fexplanation": update.fexplanation,
                "fmrkuser": fmrkuser,
                "fmrkdate": fmrkdate,
                "fitemcount": fitemcount,
                "fpiececount": fpiececount,
                "ftotalamt": ftotalamt,
                "userid": user_id,
                "fbillno": fbillno,
            })
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@data_mrk_head_router.post("/delete/{fbillno}", summary="耗材报废删除接口")
def delete_mrk_head(fbillno: str = Path(description="单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            param = {"fbillno": fbillno}
            sql = "select count(1) from i_tc_mrk_head where fbillno= :fbillno  and fbillstate>1"
            data = db.execute(text(sql), param).fetchall()

            if data[0][0] > 0:
                return BaseResponse.error(message="该订单不能删除")
            else:
                sql = "delete from i_tc_mrk_detail where fbillno= :fbillno "
                db.execute(text(sql), param)
                sql = "delete from i_tc_mrk_head where fbillno= ：fbillno "
                db.execute(text(sql), param)
                return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
